﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DBUtil
{
    public class Database
    {
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetConn()
        {
            try
            {
                //从配置文件中读取数据库连接字符串
                string xmlpath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "config.xml";
                string servername = System.Configuration.ConfigurationManager.AppSettings["servername"]; //ox.getSingalNodeValue("CostPorcess/DB/server", xmlpath);
                string dbname = System.Configuration.ConfigurationManager.AppSettings["dbname"];//ox.getSingalNodeValue("CostPorcess/DB/dbname", xmlpath);
                string userinfo = System.Configuration.ConfigurationManager.AppSettings["dbuser"];//ox.getSingalNodeValue("CostPorcess/DB/dbuser", xmlpath);
                string pwdinfo = System.Configuration.ConfigurationManager.AppSettings["dbpwd"];//ox.getSingalNodeValue("CostPorcess/DB/dbpwd", xmlpath);
                string connstr = "Server=" + servername + ";Database=" + dbname + "; User Id=" + userinfo + "; Password=" + pwdinfo;
                
                //实例化一个连接
                SqlConnection conn = new SqlConnection(connstr);
                return conn;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->GetConn");
                throw e;
            }
        }

        static public object SqlNull(object obj)
        {
            if (obj == null)
                return DBNull.Value;

            return obj;
        }
        /// <summary>
        /// 获取对应的用友数据库连接
        /// </summary>
        /// <returns></returns>
        //public SqlConnection GetUFConn()
        //{
        //    try
        //    {
        //        //从配置文件中读取数据库连接字符串
        //        //string xmlpath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "config.xml";
        //        //string servername = ox.getSingalNodeValue("CostPorcess/DB/server", xmlpath);
        //        //string dbname = Entity.CP_SData.Ufdbname;
        //        //string userinfo = ox.getSingalNodeValue("CostPorcess/DB/dbuser", xmlpath);
        //        //string pwdinfo = ox.getSingalNodeValue("CostPorcess/DB/dbpwd", xmlpath);
        //        //string connstr = "Server=" + servername + ";Database=" + dbname + "; User Id=" + userinfo + "; Password=" + pwdinfo;
        //        //实例化一个连接
        //        //SqlConnection conn = new SqlConnection(connstr);
        //        return conn;
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //}

        /// <summary>
        /// 判断数据库连接是否打开
        /// </summary>
        /// <param name="conn">数据库连接</param>
        /// <returns>
        /// flag:
        ///     true:打开
        ///     false:未打开
        /// </returns>
        public bool CheckConn(SqlConnection conn)
        {
            bool flag = false;
            if (conn.State == ConnectionState.Open)
            {
                flag = true;
            }
            return flag;
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="sconn"></param>
        public void CloseConnection(SqlConnection sconn)
        {
            if (sconn != null)
            {
                sconn.Close();
                sconn.Dispose();
            }
        }
        

        /// <summary>
        /// 执行检索语句
        /// </summary>
        /// <param name="sconn">数据库连接</param>
        /// <param name="sql">要执行的sql语句</param>
        /// <returns></returns>
        public DataTable Squery(string sql, SqlConnection sconn)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(sql, sconn);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Squery");
                throw e;
            }
        }
        /// <summary>
        /// 执行检索语句(加事务)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        /// <param name="stran">事务</param>
        /// <returns>查询结果datatable</returns>
        public DataTable Squery(string sql, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(sql, sconn);
                sda.SelectCommand.Transaction = stran;
                sda.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Squery");
                throw e;
            }
        }
        /// <summary>
        /// 执行检索语句(加事务)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        /// <param name="stran">事务</param>
        /// <returns>查询结果datatable</returns>
        public DataTable Squery(string sql, SqlConnection sconn, SqlTransaction stran,params SqlParameter[] values)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlCommand scom = new SqlCommand(sql, sconn);
                scom.Parameters.AddRange(values);
                scom.Transaction = stran;
                SqlDataAdapter sda = new SqlDataAdapter(scom);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Squery");
                throw e;
            }
        }
        /// <summary>
        /// 执行检索语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="sconn">数据库连接</param>
        /// <param name="values">需要传入的参数</param>
        /// <returns>datatable</returns>
        public DataTable Squery(string sql,SqlConnection sconn,params SqlParameter[] values)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlCommand scmd = new SqlCommand(sql, sconn);
                scmd.Parameters.AddRange(values);
                SqlDataAdapter sda = new SqlDataAdapter(scmd);
                sda.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Squery");
                throw e;
            }
        }
        /// <summary>
        /// 执行更新，删除，插入语句
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        public int Udaquery(string sql, SqlConnection sconn)
        {
            try
            {
                SqlCommand scom = new SqlCommand(sql, sconn);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(scom);
                sda.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                return 0;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Udaquery");
                throw e;
            }

        }
        /// <summary>
        /// 执行更新，删除，插入语句
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        public int Udaquery(string sql, SqlConnection sconn,params SqlParameter[] values)
        {
            try
            {
                SqlCommand scom = new SqlCommand(sql, sconn);
                scom.Parameters.AddRange(values);
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(scom);
                sda.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                return 0;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Udaquery");
                throw e;
            }

        }
        /// <summary>
        /// 执行更新，删除，插入语句(事务)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        /// <param name="strans">事务</param>
        public int Udaquery(string sql, SqlConnection sconn, SqlTransaction strans)
        {
            try
            {
                SqlCommand scom = new SqlCommand(sql, sconn);
                scom.Transaction = strans;
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(scom);
                sda.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                return 0;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Udaquery");
                throw e;
            }
        }
        /// <summary>
        /// 执行更新，删除，插入语句(事务)
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        /// <param name="strans">事务</param>
        public int Udaquery(string sql, SqlConnection sconn, SqlTransaction strans,params SqlParameter[] values)
        {
            try
            {
                SqlCommand scom = new SqlCommand(sql, sconn);
                scom.Parameters.AddRange(values);
                scom.Transaction = strans;
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter(scom);

                sda.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
                return 0;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Udaquery");
                throw e;
            }
        }
        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="sconn">数据库连接</param>
        public int GetInfol(string sql, SqlConnection sconn)
        {
            int num = 0;
            try
            {
                SqlCommand scom = new SqlCommand(sql, sconn);
                return num;
            }
            catch (Exception e)
            {
                //this.lw.error(e.Message, "database->Udaquery");
                throw e;
            }

        }
    }
}